<?php

namespace SLN0002\DAO;

use Home\DAO\BizConfigDAO;
use Home\DAO\PSIBaseExDAO;

/**
 * 总账建账 DAO
 *
 * @author PSI
 * @copyright 2015 - present
 * @license GPL v3
 */
class InitDAO extends PSIBaseExDAO
{

  /**
   * 根据科目类型获得其图标的css class
   */
  private function subjectIconCls($category)
  {
    // 1：资产、2：负债、4：所有者权益、5：成本、6：损益
    switch ($category) {
      case 1:
        return "PSI-Subject-1";
      case 2:
        return "PSI-Subject-2";
      case 4:
        return "PSI-Subject-4";
      case 5:
        return "PSI-Subject-5";
      case 6:
        return "PSI-Subject-6";
      default:
        return "PSI-Subject";
    }
  }

  /**
   * 查询明细分类账建账数据
   */
  public function queryAccDetailData($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $result = [];

    $sql = "select f.id, f.subject_code, f.db_table_name_prefix, s.category, s.name as subject_name
            from t_acc_fmt f, t_subject s
            where f.company_id = '%s' and f.subject_code = s.code
            order by f.subject_code";
    $data = $db->query($sql, $companyId);

    foreach ($data as $v) {
      $fmtId = $v["id"];
      $subjectCode = $v["subject_code"];
      $subjectName = $v["subject_name"];
      $category = $v["category"];

      $tableName = $v["db_table_name_prefix"] . "_detail";

      // 查询子账簿层级
      $sql = "select count(*) as cnt
              from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level > 0 and sub_acc_level < 4 ";
      $data = $db->query($sql, $fmtId);
      $subAccLevel = $data[0]["cnt"];
      if ($subAccLevel === 0) {
        $subAccLevel = -1;
      }

      $sql = "select sum(acc_db) as acc_db, sum(acc_cr) as acc_cr, 
                sum(acc_balance) as acc_balance, acc_balance_dbcr
              from {$tableName}
              where je_number = -1000 and company_id = '%s' and subject_code = '%s'
                and sub_acc_level = %d 
              group by acc_balance_dbcr";
      $detailData = $db->query($sql, $companyId, $subjectCode, $subAccLevel);
      foreach ($detailData as $d) {
        $result[] = [
          "id" => $subjectCode,
          "subject" => "{$subjectCode} - {$subjectName}",
          "debit" => $d["acc_db"],
          "credit" => $d["acc_cr"],
          "balance" => $d["acc_balance"],
          "balanceDir" => $d["acc_balance_dbcr"],
          "iconCls" => $this->subjectIconCls($category),
          "leaf" => true,
          "children" => [],
          "expanded" => true,
        ];
      }
    }

    return $result;
  }

  private function balanceDirCodeToName($code)
  {
    switch ($code) {
      case 1:
        return "借方";
      case 2:
        return "贷方";
      default:
        // 莫名的bug
        return "[未定义]";
    }
  }

  /**
   * 录入明细分类账期初数据
   * 
   */
  public function editAccDetailInitRecord(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $dt = $params["dt"];
    $subjectCode = $params["subjectCode"];
    $money = floatval($params["money"]);
    $balanceDir = intval($params["balanceDir"]);
    if (!in_array($balanceDir, [1, 2])) {
      return $this->badParam("balanceDir");
    }
    // 账簿扩展项
    $ex = $params["ex"];

    // 检查companyId
    $sql = "select name from t_org where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    // 检查余额方向
    $sql = "select name, balance_dir 
            from t_subject
            where is_leaf = 1 and code = '%s' and company_id = '%s' ";
    $data = $db->query($sql, $subjectCode, $companyId);
    if (!$data) {
      return $this->badParam("subjectCode");
    }
    $v = $data[0];
    $subjectName = $v["name"];
    $bd = $v["balance_dir"];

    if ($bd === 0) {
      // 借贷均可
      if (!in_array($balanceDir, [1, 2])) {
        return $this->badParam("balanceDir");
      }
    } else {
      if ($balanceDir != $bd) {
        return $this->badParam("balanceDir");
      }
    }
    $balanceDbCr = $this->balanceDirCodeToName($balanceDir);

    $sql = "select id, db_table_name_prefix 
            from t_acc_fmt 
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $this->badParam("subjectCode");
    }
    $v = $data[0];
    $tableName = $v["db_table_name_prefix"] . "_detail";
    $fmtId = $v["id"];

    // 查询子账簿层级字段
    // 目前的设计是：最多有三个层级字段
    $sql = "select id, db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level > 0 and sub_acc_level < 4
            order by sub_acc_level
            limit 3";
    $data = $db->query($sql, $fmtId);
    $subLevelFields = [];
    foreach ($data as $v) {
      $n = $v["db_field_name"];
      $cid = $v["id"];
      $voucherInput = $v["voucher_input"];

      // 从参数中查找对应的值
      foreach ($ex as $x) {
        $colId = $x["id"];
        if ($cid == $colId) {
          $value = $x["value"];
          if ($voucherInput == 2) {
            // 码表录入
            $value = $x["codeValue"];
          }

          $subLevelFields[] = [
            "name" => $n,
            "value" => $value,
          ];

          // 跳出foreach循环
          break;
        }
      }
    }

    // 检查期初日期
    // 保证$dt和已经录入的数据在一个会计期间里面
    $dtTemp = strtotime($dt);
    $initYear = date("Y", $dtTemp);
    $initMonth = date("m", $dtTemp);

    $sql = "select acc_year, acc_month, acc_detail_inited
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->bad("{$companyName}没有初始化会计期间");
    }

    $v = $data[0];
    $accDetailInited = $v["acc_detail_inited"];
    if ($accDetailInited == 1) {
      return $this->bad("已经完成明细分类账建账了，不能再录入数据");
    }
    $accYear = $v["acc_year"];
    $accMonth = $v["acc_month"];
    if ($accYear != $initYear || $accMonth != $initMonth) {
      return $this->bad("期初日期需要在{$accYear}年{$accMonth}月内");
    }

    $sql = "select voucher_year, voucher_month
            from {$tableName}
            where company_id = '%s'  
              and je_number = -1000 limit 1"; // je_number = -1000：期初建账数据
    $data = $db->query($sql, $companyId);
    if ($data) {
      $year = $data[0]["voucher_year"];
      $month = $data[0]["voucher_month"];

      if (!($year == $initYear && $month == $initMonth)) {
        return $this->bad("期初日期需要在{$year}年{$month}月内");
      }
    }

    // 先删除旧数据
    $sql = "select id
            from {$tableName}
            where company_id = '%s' and subject_code = '%s' 
              and je_number = -1000 "; // je_number = -1000：期初建账数据
    $sql .= " and sub_acc_level = %d ";
    $queryParams = [];
    $queryParams[] = $companyId;
    $queryParams[] = $subjectCode;
    // sub_acc_level参数
    $cnt = count($subLevelFields);
    if ($cnt === 0) {
      $queryParams[] = -1;
    } else {
      $queryParams[] = $cnt;
    }
    foreach ($subLevelFields as $sv) {
      $f = $sv["name"];
      $sql .= " and {$f} = '%s'";

      $queryParams[] = $sv["value"];
    }

    $data = $db->query($sql, $queryParams);
    if ($data) {
      $id = $data[0]["id"];

      // 删除建账旧数据
      $sql = "delete from {$tableName} where id = '%s' ";
      $rc = $db->execute($sql, $id);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    if (abs($money) < 0.000001) {
      // 录入金额为0，则视为用户要删除该条建账数据。(这之前的代码已经删除了建账数据了)

      // 业务日志
      $params["log"] = "组织机构[{$companyName}] - 删除科目[{$subjectCode} - {$subjectName}]明细分类账建账数据";

      // 操作成功
      $params["id"] = $id;
      return null;
    }

    // 插入建账新数据
    $id = $this->newId();

    $sql = "insert into {$tableName} (
              acc_id, company_id, subject_code, voucher_dt, voucher_year, 
              voucher_month, je_number, acc_balance_dbcr, acc_balance,
              acc_db, acc_cr, sub_acc_level, acc_flag 
            )
            values ('%s', '%s', '%s', '%s', %d,
              %d, -1000, '%s', %f,";
    if ($balanceDir == 1) {
      // 余额在借方
      $sql .= " %f, null";
    } elseif ($balanceDir == 2) {
      // 余额在贷方
      $sql .= " null, %f";
    } else {
      // 前面已经做了参数检查，不会执行到这里
    }
    $sql .= " ,%d, -1)";

    // sub_acc_level参数
    $subAccLevel = -1;
    $cnt = count($subLevelFields);
    if ($cnt === 0) {
      $subAccLevel = -1;
    } else {
      $subAccLevel = $cnt;
    }

    $rc = $db->execute(
      $sql,
      $id,
      $companyId,
      $subjectCode,
      $dt,
      $initYear,
      $initMonth,
      $balanceDbCr,
      $money,
      $money,
      $subAccLevel,
    );
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 账簿扩展项
    // 算法：上面是insert插入记录，对扩展项用的就是update
    foreach ($ex as $x) {
      $colId = $x["id"];
      $colValue = $x["value"];
      $colCodeId = $x["codeId"];
      $colCodeValue = $x["codeValue"];
      $colNameValue = $x["nameValue"];

      $sql = "select db_field_name, voucher_input
              from t_acc_fmt_cols
              where id = '%s' ";
      $data = $db->query($sql, $colId);
      if (!$data) {
        // 如果执行到这里，则是莫名bug
        continue;
      }

      $fieldName = $data[0]["db_field_name"];
      if ($fieldName == "x_v_cnt") {
        // 处理数量金额三栏账
        // 当存在字段x_v_cnt的时候，检查其他数量金额三栏账字段是否存在
        $list = [
          "x_db_cnt",
          "x_db_price",
          "x_cr_cnt",
          "x_cr_price",
          "x_balance_cnt",
          "x_balance_price",
        ];
        foreach ($list as $f) {
          $sql = "select count(*) as cnt 
                  from t_acc_fmt_cols
                  where fmt_id = '%s' and db_field_name = '%s' ";
          $dataFmt = $db->query($sql, $fmtId, $f);
          $cnt = $dataFmt[0]["cnt"];
          if ($cnt != 1) {
            return $this->bad("字段{$f}不存在，请在会计科目模块中配置该字段");
          }
        }
        $c = floatval($colValue);
        $p = 0;
        if (abs($c) < 0.0000001) {
          // 避免除零
          $p = 0;
        } else {
          $p = $money / $c;
        }

        // 数据金额三栏账大都是针对存货的，是资产类科目，余额方向应该是借方
        if ($balanceDbCr == "借方") {
          $sql = "update {$tableName}
                  set x_db_cnt = %f, x_db_price = %f,
                      x_cr_cnt = null, x_cr_price = null,
                    x_balance_cnt = %f, x_balance_price = %f
                  where acc_id = '%s' ";
          $rc = $db->execute($sql, $c, $p, $c, $p, $id);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        } else {
          return $this->bad("数据金额三栏账大都是针对存货的，是资产类科目，余额方向应该是借方");
        }

        // 用这个continue跳转到下一个扩展项
        continue;
      }

      // 下面是非数量金额三栏账的扩展项的处理逻辑
      $voucherInput = $data[0]["voucher_input"];
      if ($voucherInput == 1) {
        // 直接录入
        $sql = "update {$tableName}
                  set {$fieldName} = '%s'
                where acc_id = '%s' ";
        $rc = $db->execute($sql, $colValue, $id);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }
      } elseif ($voucherInput == 2) {
        // 码表
        $sql = "update {$tableName}
                  set {$fieldName} = '%s', {$fieldName}_name = '%s', {$fieldName}_id = '%s'
                where acc_id = '%s' ";
        $rc = $db->execute($sql, $colCodeValue, $colNameValue, $colCodeId, $id);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }
      }
    }

    // 业务日志
    $params["log"] = "组织机构[{$companyName}] - 录入科目[{$subjectCode} - {$subjectName}]明细分类账建账数据";

    // 操作成功
    $params["id"] = $id;
    return null;
  }

  /**
   * 查询账样的扩展项
   */
  public function queryFmtEx($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $result = [];

    $sql = "select name, balance_dir
            from t_subject
            where company_id = '%s' and code = '%s' and is_leaf = 1";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }
    $v = $data[0];
    $subjectName = $v["name"];
    $balanceDir = $v["balance_dir"];

    $sql = "select id 
            from t_acc_fmt 
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }

    $fmtId = $data[0]["id"];

    $sql = "select id, caption, voucher_input,
              code_table_name, voucher_input_xtype, db_field_decimal
            from t_acc_fmt_cols
            where fmt_id = '%s' and sys_col = 0 and voucher_input != 5
              and col_category != 2
            order by voucher_input_show_order ";
    $data = $db->query($sql, $fmtId);
    foreach ($data as $v) {
      $voucherInput = $v["voucher_input"];
      $fid = "";
      if ($voucherInput == 2) {
        // 码表录入
        $codeTableName = $v["code_table_name"];

        $sql = "select fid
                from t_code_table_md
                where table_name = '%s' ";
        $d = $db->query($sql, $codeTableName);
        $fid = $d[0]["fid"];
      }

      $result[] = [
        "id" => $v["id"],
        "caption" => $v["caption"],
        "voucherInput" => $voucherInput,
        "voucherInputXtype" => $v["voucher_input_xtype"],
        "fieldDec" => $v["db_field_decimal"],
        "fid" => $fid,
      ];
    }

    return [
      "subjectName" => $subjectName,
      "balanceDir" => $balanceDir,
      "fmtList" => $result
    ];
  }

  /**
   * 查询账样列
   */
  public function queryFmtColsForDetailAcc($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $result = [];

    $sql = "select id 
            from t_acc_fmt 
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }

    $fmtId = $data[0]["id"];

    $ignorFields = [
      "voucher_year",
      "voucher_month",
      "voucher_number",
      "je_number",
      "voucher_word",
      "acc_user_name",
      "biz_user_name",
      "voucher_summary"
    ];
    $sql = "select id, caption, db_field_name, col_width
            from t_acc_fmt_cols
            where fmt_id = '%s' and show_order > 0 and col_category != 1
            order by show_order ";
    $data = $db->query($sql, $fmtId);
    foreach ($data as $v) {
      $fieldName = $v["db_field_name"];
      if (in_array($fieldName, $ignorFields)) {
        continue;
      }

      $result[] = [
        "id" => $v["id"],
        "caption" => $v["caption"],
        "fieldName" => $fieldName,
        "colWidth" => $v["col_width"],
      ];
    }

    return $result;
  }

  /**
   * 查询明细分类账建账数据
   */
  public function queryDataForDetailAcc($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    // 查询物料小数位数
    $bcDAO = new BizConfigDAO($db);
    $dataScale = $bcDAO->getGoodsCountDecNumber($companyId);
    $fmt = "decimal(19, " . $dataScale . ")";

    $result = [];

    $sql = "select name, balance_dir
            from t_subject
            where company_id = '%s' and code = '%s' and is_leaf = 1";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }
    $v = $data[0];
    $subjectName = $v["name"];

    $sql = "select id, db_table_name_prefix 
            from t_acc_fmt 
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }
    $v = $data[0];
    $fmtId = $v["id"];
    $tableName = $v["db_table_name_prefix"] . "_detail";

    // 子账簿层级
    $sql = "select db_field_name
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level between 1 and 3
            order by sub_acc_level ";
    $data = $db->query($sql, $fmtId);
    $subAccLevelFields = [];
    foreach ($data as $v) {
      $subAccLevelFields[] = $v["db_field_name"];
    }

    $sqlForData = "select ";
    $fields = [];
    $ignorFields = [
      "voucher_word",
      "acc_user_name",
      "biz_user_name"
    ];
    $sql = "select id, caption, db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and show_order > 0 and col_category != 1
            order by show_order ";
    $data = $db->query($sql, $fmtId);
    $firstFlag = true;
    $listCntField = ["x_db_cnt", "x_cr_cnt", "x_balance_cnt"];
    foreach ($data as $v) {
      $fieldName = $v["db_field_name"];
      if (in_array($fieldName, $ignorFields)) {
        continue;
      }

      $voucherInput = $v["voucher_input"];

      if (!$firstFlag) {
        $sqlForData .= ", ";
      }

      if (in_array($fieldName, $listCntField)) {
        // 数量金额三栏账中的数量格式化成相应的小数位数
        $sqlForData .= "convert({$fieldName}, " . $fmt . ") as {$fieldName}";
      } else {
        $sqlForData .= $fieldName;
      }

      $firstFlag = false;

      $fields[] = [
        "name" => $fieldName,
        "voucherInput" => $voucherInput
      ];

      if ($voucherInput == 2) {
        // 码表录入
        $sqlForData .= ", {$fieldName}_name";
      }
    }

    $sqlForData .= " from " . $tableName;
    $sqlForData .= " where je_number = -1000"; // je_number = -1000：期初建账数据
    $sqlForData .= " and sub_acc_level = %d";

    $subAccLevel = count(($subAccLevelFields));
    if ($subAccLevel === 0) {
      $subAccLevel = -1;
    }

    // 按子账簿层级排序
    if (count($subAccLevelFields) > 0) {
      $sqlForData .= " order by ";
      foreach ($subAccLevelFields as $i => $v) {
        if ($i > 0) {
          $sqlForData .= ",";
        }
        $sqlForData .= $v;
      }
    }
    $data = $db->query($sqlForData, $subAccLevel);
    foreach ($data as $v) {
      $item = [];
      foreach ($fields as $f) {
        $name = $f["name"];

        // if ($name == "subject_code") {
        //   $item["subject_code"] = $v["subject_code"] . " - " . $subjectName;
        //   continue;
        // }

        $voucherInput = $f["voucherInput"];
        if ($voucherInput == 2) {
          // 码表录入
          $name2 = $name . "_name";
          $item[$name] = $v[$name] . " - " . $v[$name2];
        } else {
          $item[$name] = $v[$name];
        }
      }

      $result[] = $item;
    }

    return $result;
  }

  /**
   * 查询总账建账期间
   * 
   * 备注：原来是查询建账期间，后来改为建账期间的最后一日
   */
  public function queryAccInitYearAndMonth($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $sql = "select acc_year, acc_month
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      // 莫名的bug
      return [
        "dt" => "",
      ];
    }

    $v = $data[0];
    $year = $v["acc_year"];
    $month = $v["acc_month"];

    $daoComm = new AccCommDAO($db);
    $dt = $daoComm->buildVoucherLastDT($year, $month);

    return [
      "dt" => $dt,
    ];
  }

  /**
   * 完成明细分类账建账
   */
  public function commitAccDetailInit(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name from t_org where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    $sql = "select acc_detail_inited, acc_year, acc_month
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);

    if (!$data) {
      return $this->bad("[{$companyName}]还没有录入建账数据");
    }

    $inited = $data[0]["acc_detail_inited"];
    if ($inited == 1) {
      return $this->bad("[{$companyName}]已经完成建账，不能再次标记");
    }

    $year = $data[0]["acc_year"];
    $month = $data[0]["acc_month"];

    // 同步子账簿数据
    // 对于有子账簿的数据，在用户录入数据的时候，只录入了最明细的数据
    // 需要按子账簿层级汇总的数据，要在这里生产
    $rc = $this->syncAccDetailSub($companyId);
    if ($rc) {
      return $rc;
    }

    $sql = "update t_acc_init 
              set acc_detail_inited = 1, acc_detail_inited_dt = now()
            where company_id = '%s' ";
    $rc = $db->execute($sql, $companyId);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 更新当前会计期间
    $newYear = false;
    if ($month < 12) {
      // 月份加一
      $month++;
    } else {
      // 下一年的一月
      $year++;
      $month = 1;
      $newYear = true;
    }
    $sql = "update t_acc_current_period
            set acc_year = %d, acc_month = %d
            where company_id = '%s' ";
    $rc = $db->execute($sql, $year, $month, $companyId);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }
    if ($newYear) {
      // 初始化新一年的会计期间
      $sql = "delete from t_acc_period where company_id = '%s' and acc_year = %d";
      $rc = $db->execute($sql, $companyId, $year);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      for ($m = 1; $m < 13; $m++) {
        $sql = "insert into t_acc_period(id, company_id, acc_year, acc_month,
                  acc_gl_kept, acc_gl_closed, acc_detail_kept, acc_detail_closed,
                  period_closed, year_forward)
                values ('%s', '%s', %d, %d,
                  0, 0, 0, 0, 
                  0, 0)";
        $rc = $db->execute($sql, $this->newId(), $companyId, $year, $m);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }
      }
    }

    // 生成期初数据
    $rc = $this->genDataForAccDetailPeriodInit($companyId);
    if ($rc) {
      return $rc;
    }

    // 业务日志
    $log = "[{$companyName}]已经完成明细分类账建账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 生成明细分类账期初数据
   * 
   * 把上期期末数据复制成期初数据
   */
  private function genDataForAccDetailPeriodInit($companyId)
  {
    $db = $this->db;

    // 调用本方法的时候，当前会计期间已经调整到最新的期间了

    $accCommDAO = new AccCommDAO($db);
    $v = $accCommDAO->getCurrentAccYearAndMonth($companyId);
    if (!$v) {
      return $this->badParam("companyId");
    }
    $accYear = $v["accYear"];
    $accMonth = $v["accMonth"];

    $sql = "select id, db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $fmtId = $v["id"];
      $tableName = $v["db_table_name_prefix"] . "_detail";

      $sql = "select db_field_name, voucher_input
              from t_acc_fmt_cols
              where fmt_id = '%s' and sys_col != 1 and col_category != 1
              order by show_order";
      $dataFmtCols = $db->query($sql, $fmtId);

      $sql = "select sub_acc_level, subject_code,
                acc_db, acc_cr, acc_balance, acc_balance_dbcr";
      foreach ($dataFmtCols as $col) {
        $fieldName = $col["db_field_name"];

        $sql .= ", {$fieldName}";

        $voucherInput = $col["voucher_input"];
        if ($voucherInput == 2) {
          // 码表录入
          $sql .= ", {$fieldName}_id, {$fieldName}_name";
        }
      }

      $sql .= " from {$tableName}
              where company_id = '%s' and je_number = -1000";

      $dataInit = $db->query($sql, $companyId);
      foreach ($dataInit as $di) {
        $accBalance = $di["acc_balance"];

        $sql = "insert into {$tableName} (acc_flag, acc_id, company_id, sub_acc_level, subject_code";
        $sql .= ", voucher_dt, voucher_year, voucher_month, je_number, acc_db, acc_cr";
        $sql .= ", acc_balance, acc_balance_dbcr, voucher_summary";
        foreach ($dataFmtCols as $col) {
          $fieldName = $col["db_field_name"];

          $sql .= ", {$fieldName}";

          $voucherInput = $col["voucher_input"];
          if ($voucherInput == 2) {
            // 码表录入
            $sql .= ", {$fieldName}_id, {$fieldName}_name";
          }
        }
        $sql .= ") values (1000, '%s', '%s', %d, '%s'";
        $sql .= ", '%s', %d, %d, null, null, null";
        $sql .= ", %f, '%s', '本月期初'";
        foreach ($dataFmtCols as $col) {
          $sql .= ", '%s'";

          $voucherInput = $col["voucher_input"];
          if ($voucherInput == 2) {
            // 码表录入
            $sql .= ", '%s', '%s'";
          }
        }

        $sql .= ")";

        $insertParams = [];
        $insertParams[] = $this->newId();
        $insertParams[] = $companyId;
        $insertParams[] = $di["sub_acc_level"];
        $insertParams[] = $di["subject_code"];
        $dt = "";
        if ($accMonth < 10) {
          $dt = "{$accYear}-0{$accMonth}-01";
        } else {
          $dt = "{$accYear}-{$accMonth}-01";
        }
        $insertParams[] = $dt;
        $insertParams[] = $accYear;
        $insertParams[] = $accMonth;
        $insertParams[] = $accBalance;
        $insertParams[] = $di["acc_balance_dbcr"];
        foreach ($dataFmtCols as $col) {
          $fieldName = $col["db_field_name"];
          $insertParams[] = $di[$fieldName];

          $voucherInput = $col["voucher_input"];
          if ($voucherInput == 2) {
            // 码表录入
            $insertParams[] = $di[$fieldName . "_id"];
            $insertParams[] = $di[$fieldName . "_name"];
          }
        }

        $rc = $db->execute($sql, $insertParams);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }

        // 补丁代码，如果是三栏账，把贷方数量和单价改为null
        // 这也为了偷懒，不修改之前的代码了
        $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and db_field_name = 'x_v_cnt' ";
        $d = $db->query($sql, $fmtId);
        if ($d) {
          $sql = "update {$tableName}
                set x_cr_cnt = null, x_cr_price = null, x_db_cnt = null, x_db_price = null
                where acc_flag = 1000 and voucher_year = %d and voucher_month = %d";
          $rc = $db->execute($sql, $accYear, $accMonth);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        }
      }
    }

    // 操作成功
    return null;
  }

  /**
   * 生成总分类账期初数据
   * 
   * 把上期期末数据复制成期初数据
   */
  private function genDataForAccGlPeriodInit($companyId)
  {
    $db = $this->db;

    // 调用本方法的时候，当前会计期间已经调整到最新的期间了

    $accCommDAO = new AccCommDAO($db);
    $v = $accCommDAO->getCurrentAccYearAndMonth($companyId);
    if (!$v) {
      return $this->badParam("companyId");
    }
    $accYear = $v["accYear"];
    $accMonth = $v["accMonth"];

    // 一级科目的总账汇总数据
    $dt = "";
    if ($accMonth < 10) {
      $dt = "{$accYear}-0{$accMonth}-01";
    } else {
      $dt = "{$accYear}-{$accMonth}-01";
    }

    $sql = "select code, gl_table_name
            from t_subject
            where company_id = '%s' and parent_id is null
              and inited = 1
            order by code";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $subjectCode = $v["code"];
      $tableName = $v["gl_table_name"];
      $sql = "insert into {$tableName}
              (acc_id, company_id, acc_user_id, biz_user_id,
               subject_code, voucher_dt, voucher_year, 
               voucher_month, acc_user_name, biz_user_name,
               acc_db, acc_cr, acc_balance, acc_balance_dbcr, voucher_summary, acc_flag)
              select uuid() as acc_id, company_id, acc_user_id, biz_user_id,
                subject_code, '{$dt}' as voucher_dt, '{$accYear}' as voucher_year,
                '{$accMonth}' as voucher_month, acc_user_name, biz_user_name,
                null, null, acc_balance, acc_balance_dbcr, '本月期初' as voucher_summary,
                1000 as acc_flag 
              from {$tableName}
              where acc_id = '-1000'
              limit 1";
      $rc = $db->execute($sql);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    // 操作成功
    return null;
  }

  /**
   * 同步明细账中子账簿数据
   */
  private function syncAccDetailSub($companyId)
  {
    $db = $this->db;

    $sql = "select id, db_table_name_prefix, subject_code
            from t_acc_fmt
            where company_id = '%s' 
            order by acc_number";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $tableName = $v["db_table_name_prefix"] . "_detail";
      $fmtId = $v["id"];
      $subjectCode = $v["subject_code"];

      // 查询子账簿层级字段
      // 目前的设计是：最多有三个层级字段
      $sql = "select db_field_name, voucher_input
            from t_acc_fmt_cols
            where fmt_id = '%s' and sub_acc_level > 0 and sub_acc_level < 4
            order by sub_acc_level
            limit 3";
      $dataSub = $db->query($sql, $fmtId);
      $subLevelFields = [];
      foreach ($dataSub as $vSub) {
        $subLevelFields[] = [
          "fieldName" => $vSub["db_field_name"],
          "voucherInput" => $vSub["voucher_input"],
        ];
      }
      $subAccLevel = count($subLevelFields);
      if ($subAccLevel === 0) {
        // 没有子账簿，跳转到下一个账簿
        continue;
      }

      // 删除旧的汇总数据
      $sql = "delete from {$tableName} 
              where company_id = '%s' and subject_code = '%s' and sub_acc_level < %d 
                and je_number = -1000 ";
      $rc = $db->execute($sql, $companyId, $subjectCode, $subAccLevel);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      // 查询账样自定义列，同时把子账簿列排除掉
      $sql = "select db_field_name, voucher_input
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level = -1 and col_category != 1
                and sys_col = 0";
      $d = $db->query($sql, $fmtId);
      $exFields = [];
      foreach ($d as $ef) {
        $n = $ef["db_field_name"];
        $voucherInput = $ef["voucher_input"];

        $exFields[] = $n;

        // 码表录入
        if ($voucherInput == 2) {
          $exFields[] = $n . "_name";
          $exFields[] = $n . "_id";
        }
      }

      // 查询最明细的建账数据
      $sql = "select * from {$tableName}
              where company_id = '%s' and subject_code = '%s' and sub_acc_level = %d 
                and je_number = -1000 
              order by voucher_dt
              limit 1";
      $dataRecord = $db->query($sql, $companyId, $subjectCode, $subAccLevel);
      if (!$dataRecord) {
        // 该账没有录入期初数据
        continue;
      }

      $record = $dataRecord[0];
      // 余额借贷方向
      $balanceDbCr = $record["acc_balance_dbcr"];

      // 处理子账簿只有一级的情形
      // 此时只需要生成汇总数据即可

      // 查询汇总数据
      $sql = "select sum(acc_balance) as acc_balance from {$tableName}
              where company_id = '%s' and subject_code = '%s' and sub_acc_level = %d 
                and je_number = -1000 ";
      $d = $db->query($sql, $companyId, $subjectCode, $subAccLevel);
      $accBlanance = $d[0]["acc_balance"];

      $commFields = "acc_id, company_id, sub_acc_level, subject_code, 
                     voucher_dt, voucher_year, voucher_month, je_number,
                     acc_db, acc_cr, acc_balance, acc_balance_dbcr";
      $sqlForInsert = "insert into {$tableName}";
      $sqlForInsert .= "(";
      $sqlForInsert .= $commFields;
      foreach ($exFields as $f) {
        $sqlForInsert .= ", {$f}";
      }
      $sqlForInsert .= ") values (";
      $sqlForInsert .= "'%s', '%s', -1, '%s',";
      $sqlForInsert .= "'%s', %d, %d, -1000,";
      if ($balanceDbCr == "借方") {
        $sqlForInsert .= "%f, null, %f, '%s'";
      } elseif ($balanceDbCr == "贷方") {
        $sqlForInsert .= "null, %f, %f, '%s'";
      } else {
        return $this->badParam("balanceDbCr");
      }
      foreach ($exFields as $f) {
        $sqlForInsert .= ", '%s'";
      }
      $sqlForInsert .= ")";
      $paramsForInsert = [
        $this->newId(),
        $companyId,
        $subjectCode,
        $record["voucher_dt"],
        $record["voucher_year"],
        $record["voucher_month"],
        $accBlanance,
        $accBlanance,
        $balanceDbCr,
      ];
      foreach ($exFields as $f) {
        $paramsForInsert[] = $record[$f];
      }

      $rc = $db->execute($sqlForInsert, $paramsForInsert);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }

      // 处理子账簿有两级的情形
      // 这时候需要生成两个汇总数据
      // 最顶级的汇总数据在上面已经生成了
      // 再生成二级汇总数据即可
      if ($subAccLevel > 1) {
        // sub_acc_level == 2 || sub_acc_level == 3
        $field = $subLevelFields[0]["fieldName"];
        $fieldForKey = $field;
        $fieldList = $field;
        $fp = "'%s'";
        $vi = $subLevelFields[0]["voucherInput"];
        if ($vi == 2) {
          // 码表录入
          $fieldList = "{$field}, {$field}_id, {$field}_name";
          $fieldForKey = "{$field}_id";
          $fp = "'%s', '%s', '%s'";
        }

        $sql = "select distinct {$fieldList} 
                from {$tableName}
                where company_id = '%s' and subject_code = '%s' and sub_acc_level = %d 
                  and je_number = -1000 ";
        $dataList = $db->query($sql, $companyId, $subjectCode, $subAccLevel);
        foreach ($dataList as $r) {
          $sqlForInsert = "insert into {$tableName}";
          $sqlForInsert .= "(";
          $sqlForInsert .= $commFields;
          $sqlForInsert .= ", {$fieldList}";
          $sqlForInsert .= ") values (";
          $sqlForInsert .= "'%s', '%s', 1, '%s',";
          $sqlForInsert .= "'%s', %d, %d, -1000,";
          if ($balanceDbCr == "借方") {
            $sqlForInsert .= "%f, null, %f, '%s'";
          } elseif ($balanceDbCr == "贷方") {
            $sqlForInsert .= "null, %f, %f, '%s'";
          } else {
            return $this->badParam("balanceDbCr");
          }
          $sqlForInsert .= ", {$fp}";
          $sqlForInsert .= ")";

          $sql = "select sum(acc_balance) as acc_balance from {$tableName}
              where company_id = '%s' and subject_code = '%s' and sub_acc_level = %d 
                and je_number = -1000 and {$fieldForKey}= '%s'";
          $d = $db->query($sql, $companyId, $subjectCode, $subAccLevel, $r[$fieldForKey]);
          $accBlanance = $d[0]["acc_balance"];

          $paramsForInsert = [
            $this->newId(),
            $companyId,
            $subjectCode,
            $record["voucher_dt"],
            $record["voucher_year"],
            $record["voucher_month"],
            $accBlanance,
            $accBlanance,
            $balanceDbCr,
          ];
          if ($vi == 1) {
            $paramsForInsert[] = $r[$field];
          } elseif ($vi == 2) {
            // 码表录入
            $paramsForInsert[] = $r[$field];
            $paramsForInsert[] = $r[$field . "_id"];
            $paramsForInsert[] = $r[$field . "_name"];
          } else {
            return $this->badParam("vi");
          }

          $rc = $db->execute($sqlForInsert, $paramsForInsert);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        }
      }

      // 处理子账簿有三级的情形
      // 这时候需要生成三个汇总数据
      // 最顶级汇总数据和第二级别的汇总数据在上面已经生成了
      // 只需要生成三级汇总数据即可
      if ($subAccLevel == 3) {
        $field1 = $subLevelFields[0]["fieldName"];
        $fieldForKey1 = $field1;
        $fieldList = $field1;
        $fp = "'%s'";
        $vi = $subLevelFields[0]["voucherInput"];
        if ($vi == 2) {
          // 码表录入
          $fieldList = "{$field1}, {$field1}_id, {$field1}_name";
          $fieldForKey1 = "{$field1}_id";
          $fp = "'%s', '%s', '%s'";
        }

        $field2 = $subLevelFields[1]["fieldName"];
        $fieldForKey2 = $field2;
        $vi = $subLevelFields[1]["voucherInput"];
        if ($vi == 2) {
          // 码表录入
          $fieldList .= ", {$field2}, {$field2}_id, {$field2}_name";
          $fieldForKey2 = "{$field2}_id";
          $fp .= ", '%s', '%s', '%s'";
        } else if ($vi == 1) {
          $fieldList .= ", {$field2}";
          $fp .= ", '%'";
        }

        $sql = "select distinct {$fieldList} 
                from {$tableName}
                where company_id = '%s' and subject_code = '%s' and sub_acc_level = %d 
                  and je_number = -1000 ";
        $dataList = $db->query($sql, $companyId, $subjectCode, $subAccLevel);
        foreach ($dataList as $r) {
          $sqlForInsert = "insert into {$tableName}";
          $sqlForInsert .= "(";
          $sqlForInsert .= $commFields;
          $sqlForInsert .= ", {$fieldList}";
          $sqlForInsert .= ") values (";
          $sqlForInsert .= "'%s', '%s', 2, '%s',";
          $sqlForInsert .= "'%s', %d, %d, -1000,";
          if ($balanceDbCr == "借方") {
            $sqlForInsert .= "%f, null, %f, '%s'";
          } elseif ($balanceDbCr == "贷方") {
            $sqlForInsert .= "null, %f, %f, '%s'";
          } else {
            return $this->badParam("balanceDbCr");
          }
          $sqlForInsert .= ", {$fp}";
          $sqlForInsert .= ")";

          $sql = "select sum(acc_balance) as acc_balance from {$tableName}
              where company_id = '%s' and subject_code = '%s' and sub_acc_level = %d 
                and je_number = -1000 and {$fieldForKey1}= '%s' and {$fieldForKey2} = '%s'";
          $d = $db->query($sql, $companyId, $subjectCode, $subAccLevel, $r[$fieldForKey1], $r[$fieldForKey2]);
          $accBlanance = $d[0]["acc_balance"];

          $paramsForInsert = [
            $this->newId(),
            $companyId,
            $subjectCode,
            $record["voucher_dt"],
            $record["voucher_year"],
            $record["voucher_month"],
            $accBlanance,
            $accBlanance,
            $balanceDbCr,
          ];
          $vi = $subLevelFields[0]["voucherInput"];
          if ($vi == 1) {
            $paramsForInsert[] = $r[$field1];
          } elseif ($vi == 2) {
            // 码表录入
            $paramsForInsert[] = $r[$field1];
            $paramsForInsert[] = $r[$field1 . "_id"];
            $paramsForInsert[] = $r[$field1 . "_name"];
          } else {
            return $this->badParam("vi");
          }

          $vi = $subLevelFields[1]["voucherInput"];
          if ($vi == 1) {
            $paramsForInsert[] = $r[$field2];
          } elseif ($vi == 2) {
            // 码表录入
            $paramsForInsert[] = $r[$field2];
            $paramsForInsert[] = $r[$field2 . "_id"];
            $paramsForInsert[] = $r[$field2 . "_name"];
          } else {
            return $this->badParam("vi");
          }

          $rc = $db->execute($sqlForInsert, $paramsForInsert);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        }
      }


      $sql = "select db_field_name
              from t_acc_fmt_cols
              where fmt_id = '%s' and db_field_name = 'x_v_cnt' ";
      $d = $db->query($sql, $fmtId);
      if ($d) {
        // 三栏账
        $sql = "select acc_balance_dbcr
                from {$tableName}
                where je_number = -1000
                order by id
                limit 1";
        $d = $db->query($sql);
        if (!$d) {
          return $this->bad("莫名的bug");
        }
        $dbcr = $d[0]["acc_balance_dbcr"];
        if ($dbcr == "借方") {
          // 这段代码属于补丁代码，把三栏账中借方数量和单价改为null
          // 之前的代码把这两个字段写成0了，懒得去修改之前的代码了
          $sql = "update {$tableName}
                  set x_cr_cnt = null, x_cr_price = null
                  where je_number = -1000";
          $rc = $db->execute($sql);
          if ($rc === false) {
            return $this->sqlError(__METHOD__, __LINE__);
          }
        } else {
          return $this->bad("三栏账期初余额方向不能是贷方");
        }
      }
    } // end of foreach ($data as $v)

    // 操作成功
    return null;
  }

  /**
   * 取消明细分类账建账
   */
  public function cancelAccDetailInit(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name from t_org where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    $sql = "select acc_detail_inited, acc_gl_inited
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->bad("[{$companyName}]还没有录入建账数据");
    }

    $inited = $data[0]["acc_detail_inited"];
    if ($inited == 0) {
      return $this->bad("[{$companyName}]还没有标记建账完毕，无须进行取消操作");
    }

    $glInited = $data[0]["acc_gl_inited"];
    if ($glInited == 1) {
      return $this->bad("[{$companyName}]的总分类账已经完成建账，此时不能取消明细分类账建账");
    }

    $acDAO = new AccCommDAO($db);
    $v = $acDAO->getCurrentAccYearAndMonth($companyId);
    if (!$v) {
      return $this->bad("[{$companyName}]还没有录入建账数据");
    }
    $accYear = $v["accYear"];
    $accMonth = $v["accMonth"];

    // 检查是否有了日常的业务，如果有则不能取消标志
    $sql = "select db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $tableName = $v["db_table_name_prefix"] . "_detail";

      $sql = "select count(*) as cnt from {$tableName} where je_number > 0";
      $d = $db->query($sql);
      $cnt = $d["cnt"];
      if ($cnt > 0) {
        return $this->bad("已经有了日常业务发生，不能取消建账标志了");
      }

      // 删除期初数据
      $sql = "delete from {$tableName} 
              where je_number is null and company_id = '%s' 
                and voucher_year = %d and voucher_month = %d";
      $rc = $db->execute($sql, $companyId, $accYear, $accMonth);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    $sql = "update t_acc_init 
              set acc_detail_inited = 0, acc_detail_inited_dt = null
            where company_id = '%s' ";
    $rc = $db->execute($sql, $companyId);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 把当前会计期间回滚到上一个月
    $sql = "select acc_year, acc_month
            from t_acc_current_period
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->bad("companyId");
    }
    $accYear = $data[0]["acc_year"];
    $accMonth = $data[0]["acc_month"];
    $accMonth -= 1;
    if ($accMonth < 1) {
      $accYear -= 1;
      $accMonth = 12;
    }
    $sql = "update t_acc_current_period
              set acc_year = %d, acc_month = %d
            where company_id = '%s' ";
    $rc = $db->execute($sql, $accYear, $accMonth, $companyId);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[{$companyName}]取消明细分类账建账完成标记";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 查询建账完成情况
   * 
   */
  public function queryInitStatus($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $sql = "select acc_year, acc_month, acc_detail_inited, acc_detail_inited_dt,
              acc_gl_inited, acc_gl_inited_dt
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->emptyResult();
    }
    $v = $data[0];

    $result = [
      "year" => $v["acc_year"],
      "month" => $v["acc_month"],
      "detailInited" => $v["acc_detail_inited"],
      "detailDT" => $v["acc_detail_inited_dt"],
      "glInited" => $v["acc_gl_inited"],
      "glDT" => $v["acc_gl_inited_dt"],
    ];

    return $result;
  }

  /**
   * 生成总账初始化数据
   */
  private function genInitData($companyId)
  {
    $db = $this->db;

    // 查询建账的会计期间，注意不能用当前会计期间，因为在明细账建账中已经把当前会计期间修改了
    $sql = "select acc_year, acc_month
              from t_acc_init 
              where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      // 莫名的bug
      return $this->badParam("companyId");
    }
    $v = $data[0];
    $accYear = $v["acc_year"];
    $accMonth = $v["acc_month"];

    $daoComm = new AccCommDAO($db);
    $accDT = $daoComm->buildPeriodStr($accYear, $accMonth) . "-01";

    $sql = "select code, gl_table_name, balance_dir
            from t_subject
            where company_id = '%s' and parent_id is null
              and inited = 1
            order by code";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $subjectCode = $v["code"];
      $tableName = $v["gl_table_name"];
      $balanceDir = $v["balance_dir"];

      // 清除旧数据
      $sql = "delete from {$tableName} ";
      $rc = $db->execute($sql);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }


      // 汇总一级科目的所有子科目

      $sql = "select code
              from t_subject
              where company_id = '%s' and left(code, 4) = '%s' 
                and is_leaf = 1";
      $dataLeaf = $db->query($sql, $companyId, $subjectCode);
      $sumDb = 0;
      $sumCr = 0;
      foreach ($dataLeaf as $dl) {
        $sql = "select db_table_name_prefix
                from t_acc_fmt
                where company_id = '%s' and subject_code = '%s' ";
        $dataRecord = $db->query($sql, $companyId, $dl["code"]);
        if (!$dataRecord) {
          // 莫名的bug
          continue;
        }

        $tableNameForSum = $dataRecord[0]["db_table_name_prefix"] . "_detail";
        $sql = "select acc_db, acc_cr
                from {$tableNameForSum}
                where je_number = -1000 and sub_acc_level = -1";
        $dataSum = $db->query($sql);
        if (!$dataSum) {
          // 莫名的bug
          continue;
        }

        $v = $dataSum[0];
        $accDb = $v["acc_db"];
        $accCr = $v["acc_cr"];
        if ($accDb) {
          $sumDb += $accDb;
        }
        if ($accCr) {
          $sumCr += $accCr;
        }
      }

      $sumDb = floatval($sumDb);
      $sumCr = floatval($sumCr);
      $isDb = -1;
      if (abs($sumDb) > 0.000001) {
        // sumDb != 0
        $isDb = 1;
      } else {
        if (abs($sumCr) > 0.000001) {
          // sumCr != 0
          $isDb = 2;
        } else {
          // sumDb 和sumCr都为0
          // 莫名的bug
          $isDb = -1;
        }
      }
      if ($isDb == -1) {
        continue;
      }

      $balance = $sumDb;
      $dir = "借方";
      if ($isDb == 2) {
        $balance = $sumCr;
        $dir = "贷方";
      }
      if ($balanceDir == 0) {
        // 借贷均可
        // 不做调整
      } elseif ($balanceDir == 1) {
        $dir = "借方";
      } elseif ($balanceDir == 2) {
        $dir = "贷方";
      }

      $sql = "insert into {$tableName}
              (acc_id, company_id, subject_code, voucher_dt, voucher_year, voucher_month, 
                acc_db, acc_cr, acc_balance, acc_balance_dbcr, voucher_summary, acc_flag)
              values ('%s', '%s', '%s', '%s', %d, %d, ";
      if ($isDb == 1) {
        $sql .= "%f, null, ";
      } else {
        $sql .= "null, %f, ";
      }
      $sql .= "%f, '%s', '期初建账', -1";
      $sql .= ")";

      $queryParams = [];
      $queryParams[] = "-1000"; // acc_id，在建账的记录用-1000标志
      $queryParams[] = $companyId;
      $queryParams[] = $subjectCode;
      $queryParams[] = $accDT;
      $queryParams[] = $accYear;
      $queryParams[] = $accMonth;
      $queryParams[] = $balance; // 借方金额or贷方金额
      $queryParams[] = $balance; // 余额
      $queryParams[] = $dir;

      $rc = $db->execute($sql, $queryParams);
      if ($rc === false) {
        return $this->sqlError(__METHOD__, __LINE__);
      }
    }

    // 操作成功
    return null;
  }

  /**
   * 从明细分类账生成总账建账数据
   */
  public function genInitDataFromAccDetail(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    $sql = "select acc_detail_inited
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 明细分类账还没有录入数据");
    }
    $inited = $data[0]["acc_detail_inited"];
    if ($inited != 1) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 明细分类账还没有完成建账");
    }

    $rc = $this->genInitData($companyId);
    if ($rc) {
      return $rc;
    }

    // 业务日志
    $log = "[$companyName] - 从明细分类账生成总分类账建账数据";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 查询总分类账建账数据
   */
  public function queryAccGlData($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $result = [];

    $sql = "select f.subject_code, f.db_table_name_prefix, s.category, s.name as subject_name
            from t_acc_fmt f, t_subject s
            where f.company_id = '%s' and f.subject_code = s.code
            order by f.subject_code";
    $data = $db->query($sql, $companyId);

    foreach ($data as $v) {
      $subjectCode = $v["subject_code"];
      $subjectName = $v["subject_name"];
      $category = $v["category"];

      $tableName = $v["db_table_name_prefix"];
      $sql = "select sum(acc_db) as acc_db, sum(acc_cr) as acc_cr, 
                sum(acc_balance) as acc_balance, acc_balance_dbcr
              from {$tableName}
              where je_number = -1000 and company_id = '%s' and subject_code = '%s'
                and sub_acc_level = -1 
              group by acc_balance_dbcr";
      $detailData = $db->query($sql, $companyId, $subjectCode);
      foreach ($detailData as $d) {
        $result[] = [
          "id" => $subjectCode,
          "subject" => "{$subjectCode} - {$subjectName}",
          "debit" => $d["acc_db"],
          "credit" => $d["acc_cr"],
          "balance" => $d["acc_balance"],
          "balanceDir" => $d["acc_balance_dbcr"],
          "iconCls" => $this->subjectIconCls($category),
          "leaf" => true,
          "children" => [],
          "expanded" => true,
        ];
      }
    }

    return $result;
  }

  /**
   * 查询总分类账建账数据
   */
  public function queryDataForGlAcc($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $sql = "select code, name, gl_table_name
            from t_subject
            where company_id = '%s' and parent_id is null
            order by code";
    $data = $db->query($sql, $companyId);

    $result = [];
    foreach ($data as $v) {
      $subjectCode = $v["code"];
      $subjectName = $v["name"];
      $tableName = $v["gl_table_name"];

      $sql = "select acc_db, acc_cr, acc_balance, acc_balance_dbcr
              from {$tableName}
              where acc_id = '-1000'
              limit 1";
      $dataGL = $db->query($sql);
      if (!$dataGL) {
        continue;
      }

      $it = $dataGL[0];

      $result[] = [
        "id" => $subjectCode,
        "code" => $subjectCode,
        "name" => $subjectName,
        "debit" => $it["acc_db"],
        "credit" => $it["acc_cr"],
        "balance" => $it["acc_balance"],
        "balanceDir" => $it["acc_balance_dbcr"],
      ];
    }

    return $result;
  }

  /**
   * 完成总分类账建账
   * 
   * 完成总分类账建账的之前，当前会计期间就已经处于一下个会计期间了（这个是在明细账建账完成时候修改的）
   */
  public function commitAccGlInit(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    // 检查明细分类账建账是否完成
    $sql = "select acc_detail_inited, acc_gl_inited
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 明细分类账还没有录入数据");
    }
    $detailInited = $data[0]["acc_detail_inited"];
    if ($detailInited != 1) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 明细分类账还没有完成建账");
    }

    $glInited = $data[0]["acc_gl_inited"];
    if ($glInited == 1) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 总分类账已经完成建账了");
    }

    // 把数据重新生成一遍
    $rc = $this->genInitData($companyId);
    if ($rc) {
      return $rc;
    }

    // 试算平衡
    $queryParams = [
      "companyId" => $companyId,
    ];

    $r = $this->queryAccGlBalanceData($params);
    $isBalance = $r["success"];
    if (!$isBalance) {
      return $this->bad("期初借方合计与期初贷方合计不相等，无法完成总分类账建账");
    }

    $sql = "update t_acc_init
            set acc_gl_inited = 1, acc_gl_inited_dt = now()
            where company_id = '%s' ";
    $rc = $db->execute($sql, $companyId);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 生成期初数据
    $rc = $this->genDataForAccGlPeriodInit($companyId);
    if ($rc) {
      return $rc;
    }

    // 业务日志
    $log = "[$companyName] - 总分类账完成建账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 取消总分类账建账
   * 
   * 取消总分类账建账的后，当前会计期间仍是处于一下个会计期间的
   * （只有取消明细分类账建账才回滚会计期间）
   */
  public function cancelAccGlInit(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name 
            from t_org
            where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    $sql = "select acc_gl_inited
            from t_acc_init
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 总分类账还没有完成建账，无需取消建账");
    }
    $inited = $data[0]["acc_gl_inited"];
    if ($inited == 0) {
      return $this->bad("<span style='color:red'>{$companyName}</span> 总分类账还没有完成建账，无需取消建账");
    }

    $acDAO = new AccCommDAO($db);
    $v = $acDAO->getCurrentAccYearAndMonth($companyId);
    if (!$v) {
      return $this->bad("[{$companyName}]还没有录入建账数据");
    }
    $accYear = $v["accYear"];
    $accMonth = $v["accMonth"];

    // 检查是否有了日常的业务，如果有则不能取消标志
    $sql = "select db_table_name_prefix
        from t_acc_fmt
        where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $tableName = $v["db_table_name_prefix"] . "_detail";

      $sql = "select count(*) as cnt from {$tableName} where je_number > 0";
      $d = $db->query($sql);
      $cnt = $d[0]["cnt"];
      if ($cnt > 0) {
        return $this->bad("已经有了日常业务发生，不能取消建账标志了");
      }
    }

    $sql = "update t_acc_init 
            set acc_gl_inited = 0, acc_gl_inited_dt = null
            where company_id = '%s' ";
    $rc = $db->execute($sql, $companyId);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[$companyName] - 取消总分类账建账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 期初余额试算平衡
   */
  public function queryAccGlBalanceData($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];

    $sql = "select gl_table_name
            from t_subject
            where company_id = '%s' and parent_id is null
            order by code";
    $data = $db->query($sql, $companyId);

    $sumDb = 0;
    $sumCr = 0;

    foreach ($data as $v) {
      $tableName = $v["gl_table_name"];

      $sql = "select acc_db, acc_cr
              from {$tableName}
              where acc_id = '-1000'
              limit 1";
      $dataGL = $db->query($sql);
      if (!$dataGL) {
        continue;
      }

      $it = $dataGL[0];

      $sumDb += floatval($it["acc_db"]);
      $sumCr += floatval($it["acc_cr"]);
    }

    $isBalance = abs($sumDb - $sumCr) < 0.0000001;
    $dataList = [
      "debit" => $sumDb,
      "credit" => $sumCr,
      "balance" => $isBalance ? "平衡" : "<strong style='color:red'>不平衡</strong>"
    ];

    if (abs($sumDb) < 0.0000001 && abs($sumCr) < 0.0000001) {
      // 合计都是0，就是没有录入数据
      $dataList = [];
    }

    $result = [];

    $result["success"] = $isBalance;
    $result["dataList"] = $dataList;
    return $result;
  }
}
